﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Http;
namespace OMG.Fill.Frunit.Model.UI.Controllers
{
    public class FruProSurveyController : ApiController
    {
        FruProSurveyServices fruProSurveyServices = new FruProSurveyServices();
        
        //[ApiActionAuthorize("获取表3生产情况调查表T_FruProSurvey分页列表对象操作")]
        //[ApiActionException]
        public Pager<List<FruProSurveyEntity>> PostPageList(FruProSurveyQuery query)
        {
            return fruProSurveyServices.GetPageList(query);
        }
        //[ApiActionAuthorize("新增表3生产情况调查表T_FruProSurvey指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Post(FruProSurveyEntity model) {
            return fruProSurveyServices.AddInfo(model);
        }
        //[ApiActionAuthorize("修改表3生产情况调查表T_FruProSurvey指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Put(List<FruProSurveyEntity> model)
        {
            return fruProSurveyServices.UpdateInfo(model);
        }
        //[ApiActionAuthorize("删除表3生产情况调查表T_FruProSurvey指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Delete(string id)
        {
            return fruProSurveyServices.DeleteInfo(id);
        }
        //[ApiActionAuthorize("获取表3生产情况调查表T_FruProSurvey指定对象操作")]
        //[ApiActionException]
        public ResResult<FruProSurveyEntity> Get(string id)
        {
            return fruProSurveyServices.GetModel(id);
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(string ReportId)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("FruProSurvey") as HSSFSheet;//读取模板所在sheet位置
            //ResResult<List<FruProSurveyEntity>> pagelist = fruProSurveyServices.GetInfo(ReportId);//数据获取
            ResResult<List<FruProSurveyEntity>> pagelist = fruProSurveyServices.GetDataInfo(ReportId);
            for (int i = 0; i < pagelist.model.Count; i++)
            {               
                try
                {
                    HSSFRow row = (HSSFRow)sheet.GetRow(4);//读取模板第4行
                    var style = row.GetCell(2).CellStyle;//读取模板第4行第一单元格样式并保存
                    row.GetCell(2).CellStyle = style;//对第4行第一单元格设置原来样式
                    HSSFRow datarow = (HSSFRow)sheet.GetRow(i + 4);
                    datarow.GetCell(1).SetCellValue(pagelist.model[i].GrandArea == null ? "" : string.Format("{0:N1}", pagelist.model[i].GrandArea));
                    datarow.GetCell(2).SetCellValue(pagelist.model[i].CumYield == null ? "" : string.Format("{0:N1}", pagelist.model[i].CumYield));
                    datarow.GetCell(3).SetCellValue(pagelist.model[i].AvgMuYield == null ? "" : string.Format("{0:N1}", pagelist.model[i].AvgMuYield));
                    datarow.GetCell(4).SetCellValue(pagelist.model[i].NewArea == null ? "" : string.Format("{0:N1}", pagelist.model[i].NewArea));
                    datarow.GetCell(5).SetCellValue(pagelist.model[i].FOrchard == null ? "" : string.Format("{0:N1}", pagelist.model[i].FOrchard));
                    datarow.GetCell(6).SetCellValue(pagelist.model[i].FTOrchard == null ? "" : string.Format("{0:N1}", pagelist.model[i].FTOrchard));
                    datarow.GetCell(7).SetCellValue(pagelist.model[i].TOrchard == null ? "" : string.Format("{0:N1}", pagelist.model[i].TOrchard));
                    datarow.GetCell(8).SetCellValue(pagelist.model[i].Remark == null ? "" : pagelist.model[i].Remark);
                    //datarow.CreateCell(2).SetCellValue(pagelist.model[i].GrandArea == null ? "" : pagelist.model[i].GrandArea.ToString());
                    //datarow.CreateCell(3).SetCellValue(pagelist.model[i].CumYield == null ? "" : pagelist.model[i].CumYield.ToString());
                    //datarow.CreateCell(4).SetCellValue(pagelist.model[i].NewArea == null ? "" : pagelist.model[i].NewArea.ToString());
                    //datarow.CreateCell(5).SetCellValue(pagelist.model[i].FOrchard == null ? "" : pagelist.model[i].FOrchard.ToString());
                    //datarow.CreateCell(6).SetCellValue(pagelist.model[i].FTOrchard == null ? "" : pagelist.model[i].FTOrchard.ToString());
                    //datarow.CreateCell(7).SetCellValue(pagelist.model[i].TOrchard == null ? "" : pagelist.model[i].TOrchard.ToString());
                    datarow.GetCell(1).CellStyle = style;
                    datarow.GetCell(2).CellStyle = style;
                    datarow.GetCell(3).CellStyle = style;
                    datarow.GetCell(4).CellStyle = style;
                    datarow.GetCell(5).CellStyle = style;
                    datarow.GetCell(6).CellStyle = style;
                    datarow.GetCell(7).CellStyle = style;
                    datarow.GetCell(8).CellStyle = style;

                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog(ex.ToString().Trim());
                }
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        ///  导出下载响应
        /// </summary>
        /// <param name="ReportId"></param>
        /// <param name="FillInYear"></param>
        public void GetExcelData(string ReportId, string FillInYear,string AreaCode)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            //curContext.Response.Charset = "GB2312";//防止中文出现乱码
            //System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);//防止中文出现乱码
            //curContext.Response.ContentEncoding = Encoding.GetEncoding("GB2312");  //设置输出流为简体中文 
            string strFileName = "（二）"+FillInYear +"年-"+ AreaName+ "-主要水果生产情况年度面积产量统计表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //    "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(Method(ReportId).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="ReportId"></param>
        /// <returns></returns>
        public ResResult<List<FruProSurveyEntity>> PostImportData(string ReportId)
        {
            ReportServices reportServices = new ReportServices();
            ResResult<List<FruProSurveyEntity>> obj = new ResResult<List<FruProSurveyEntity>>();            
            HttpContext curContext = HttpContext.Current;
            HttpFileCollection files = curContext.Request.Files;
            if (curContext.Request.Files.Count != 0)
            {
                HttpPostedFile upfile = files[0];
                if (upfile != null && upfile.ContentLength > 0)
                {
                    string temppath = curContext.Server.MapPath("~/App_Data/temppath");
                    if (!Directory.Exists(temppath))//判断文件夹是否存在 
                    {
                        Directory.CreateDirectory(temppath);//不存在则创建文件夹 
                    }
                    string filetype = Path.GetExtension(upfile.FileName);
                    string filenewName = Guid.NewGuid().ToString().Replace("-", "") + filetype;
                    string strpath = Path.Combine(temppath, filenewName);
                    upfile.SaveAs(strpath);                   
                    List<FruProSurveyEntity> ProSurveyList = fruProSurveyServices.GetDataInfo(ReportId).model;
                    List<FruProSurveyEntity> excelList = fruProSurveyServices.ExcelToModel(strpath, filetype, "", 4);
                    List<FruProSurveyEntity> templateList = fruProSurveyServices.GetContent(ReportId);//获取到模板excel,用于跟导入的数据比较，看类型位置是否发生改动
                    FruProSurveyEntity pentity = excelList[2];
                    pentity.GrandArea = 0;
                    pentity.CumYield = 0;
                    pentity.NewArea = 0;
                    pentity.FOrchard = 0;
                    pentity.FTOrchard = 0;
                    pentity.TOrchard = 0;
                    for (int k = 3; k <= 8; k++)
                    {
                        if (excelList[k].GrandArea != null)
                            pentity.GrandArea += excelList[k].GrandArea;
                        if (excelList[k].CumYield != null)
                            pentity.CumYield += excelList[k].CumYield;
                        if (excelList[k].NewArea != null)
                            pentity.NewArea += excelList[k].NewArea;
                        if (excelList[k].FOrchard != null)
                            pentity.FOrchard += excelList[k].FOrchard;
                        if (excelList[k].FTOrchard != null)
                            pentity.FTOrchard += excelList[k].FTOrchard;
                        if (excelList[k].TOrchard != null)
                            pentity.TOrchard += excelList[k].TOrchard;
                    }                   
                    if(excelList[2].GrandArea < pentity.GrandArea || excelList[2].CumYield < pentity.CumYield || excelList[2].NewArea < pentity.NewArea ||
                        excelList[2].FOrchard < pentity.FOrchard || excelList[2].FTOrchard < pentity.FTOrchard || excelList[2].TOrchard < pentity.TOrchard)
                    {
                        obj.message = "您的导入文件柑橘项小于其子项之和！";
                        obj.status = DefaultKey.ErrorResult;
                        return obj;
                    }
                    for (int i = 0;i< templateList.Count; i++)
                    {
                        if(excelList[i].Variety != templateList[i].Variety)
                        {                           
                            obj.message = "您的导入文件已发生改变，无法正常导入数据，请重新下载模板。";
                            obj.status = DefaultKey.ErrorResult;
                            return obj;
                        }
                    }
                    for (int i = 0; i < ProSurveyList.Count; i++)
                    {
                        ProSurveyList[i].GrandArea = excelList[i].GrandArea;
                        ProSurveyList[i].CumYield = excelList[i].CumYield;
                        ProSurveyList[i].AvgMuYield = excelList[i].AvgMuYield;
                        ProSurveyList[i].NewArea = excelList[i].NewArea;
                        ProSurveyList[i].FOrchard = excelList[i].FOrchard;
                        ProSurveyList[i].FTOrchard = excelList[i].FTOrchard;
                        ProSurveyList[i].TOrchard = excelList[i].TOrchard;
                        ProSurveyList[i].Remark = excelList[i].Remark;
                    }
                    fruProSurveyServices.UpdateInfo(ProSurveyList);
                    reportServices.UpStatus(ReportId, "2", "");
                    obj.model = ProSurveyList;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "数据导入成功！！";
                    Directory.Delete(temppath, true);
                }
            }
            return obj;
        }
        /// <summary>
        /// 获取主要水果品种字典
        /// </summary>
        /// <returns></returns>
        public ResResult<List<FruProSurveyEntity>> GetVarietyDic()
        {
            ResResult<List<FruProSurveyEntity>> obj = new ResResult<List<FruProSurveyEntity>>();
            List<FruProSurveyEntity> list = new List<FruProSurveyEntity>();
            List < FruProSurveyEntity > templist = fruProSurveyServices.GetContent("");//此时合计位于最后一行
            int len = templist.Count;
            //FruProSurveyEntity totalmodel = templist[len - 1];
            //totalmodel.Variety = "全部";
            //templist.RemoveAt(len-1);
            //list.Add(totalmodel);
            //list.AddRange(templist);
            obj.model = templist;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "";
            return obj;
        }
        /// <summary>
        /// 获取统计的列表数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<ProSurveyStatisModel>> GetStatisListData(string AreaCode, string FillInYear, string VarietyCode)
        {
            if (string.IsNullOrEmpty(VarietyCode))
            {
                ResResult<List<ProSurveyStatisModel>> obj = new ResResult<List<ProSurveyStatisModel>>();
                List<ProSurveyStatisModel> list = new List<ProSurveyStatisModel>();
                List<FruProSurveyEntity> varietyList = fruProSurveyServices.GetContent("");
                for (int i = 0; i < varietyList.Count; i++)
                {
                    string code = varietyList[i].VarietyCode;
                    ProSurveyStatisModel model = new ProSurveyStatisModel();
                    ProSurveyStatisModel tempmodel;
                    if (AreaCode.Equals("530000000000"))
                    {
                        tempmodel = fruProSurveyServices.getTProvinStatis(FillInYear, code);
                    }
                    else if (AreaCode.Substring(4, 8).Equals("00000000"))
                    {
                        tempmodel = fruProSurveyServices.getProvinStatis(AreaCode, FillInYear, code);
                    }
                    else
                    {
                        tempmodel = fruProSurveyServices.getCountyStatisList(AreaCode, FillInYear, code)[0];
                    }
                    if(tempmodel != null)
                    {
                        model = tempmodel;
                    }
                    model.Variety = varietyList[i].Variety;
                    model.ParentCode = varietyList[i].ParentCode;
                    model.VarietyCode = code;
                    list.Add(model);
                }
                obj.model = list;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "获取成功！";
                return obj;
            }
            else
            {
                return fruProSurveyServices.getStatisListData(AreaCode, FillInYear, VarietyCode);
            }
            
        }


        public void GetStatisExcel(string AreaCode, string FillInYear, string DownType)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string strFileName = FillInYear + "年-"+ AreaName  + "-生产情况汇总表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(loadStatis(AreaCode, FillInYear,DownType).GetBuffer());
            curContext.Response.End();
        }

        public MemoryStream loadStatis(string AreaCode, string FillInYear,string DownType)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\FruStatisDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("FruProSurvey1") as HSSFSheet;//读取模板所在sheet位置

            if (DownType.Equals("1"))
            {
                List<ProSurveyStatisModel> totallist = GetStatisListData(AreaCode, FillInYear, null).model;//数据获取
                sheet = CopyToSheet(sheet, totallist, DownType);                
                AreaServices areaServices = new AreaServices();
                string areaName = areaServices.GetByCodeModel(AreaCode).model.Name;
                sheet.CopyTo(workbook, areaName, true, true);
                List<ProSurveyStatisModel> childlist = new List<ProSurveyStatisModel>();
                if (AreaCode.Equals("530000000000"))
                {
                    var citys = areaServices.GetPrefectureList();
                    foreach (string areacode in citys)
                    {
                        childlist = (List<ProSurveyStatisModel>)GetStatisListData(areacode, FillInYear, null).model;
                        FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook Book = new HSSFWorkbook(fm);
                        HSSFSheet temp = Book.GetSheet("FruProSurvey1") as HSSFSheet;
                        HSSFSheet sheet1 = CopyToSheet(temp, childlist, DownType);
                        string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
                        sheet1.CopyTo(workbook, areaName1, true, true);
                    }
                    //新增县级合计总表
                    List<ProSurveyStatisModel> sumlist = fruProSurveyServices.getOutStatisList(AreaCode, FillInYear, "sum").model;
                    HSSFSheet vsheet = templateBook.GetSheet("FruProSurvey") as HSSFSheet;
                    vsheet = CopyToSheet(vsheet, sumlist, "2");
                    var eep = vsheet.GetRowEnumerator();
                    vsheet.CopyTo(workbook, "合计", true, true);
                }
                else if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    var countys = areaServices.GetChildList(AreaCode);
                    foreach (string areacode in countys)
                    {
                        childlist = (List<ProSurveyStatisModel>)fruProSurveyServices.getCountyStatisList(areacode, FillInYear, null);
                        FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook Book = new HSSFWorkbook(fm);
                        HSSFSheet temp = Book.GetSheet("FruProSurvey1") as HSSFSheet;
                        HSSFSheet sheet1 = CopyToSheet(temp, childlist, DownType);
                        string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
                        sheet1.CopyTo(workbook, areaName1, true, true);
                    }
                }
            }
            else
            {
                List<FruProSurveyEntity> varietyList = fruProSurveyServices.GetContent("");
                /* HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("全年累计面积");
                 HSSFSheet sheet2 = (HSSFSheet)workbook.CreateSheet("全年累计产量");
                 HSSFSheet sheet3 = (HSSFSheet)workbook.CreateSheet("平均亩产");
                 HSSFSheet sheet4 = (HSSFSheet)workbook.CreateSheet("本年新建果园面积");
                 HSSFSheet sheet5 = (HSSFSheet)workbook.CreateSheet("树龄5年以下果园面积");
                 HSSFSheet sheet6 = (HSSFSheet)workbook.CreateSheet("树龄5-20年果园面积");
                 HSSFSheet sheet7 = (HSSFSheet)workbook.CreateSheet("树龄20年以上果园面积");           
                 HSSFRow vrow1 = (HSSFRow)sheet1.CreateRow(3);
                 HSSFRow vrow2 = (HSSFRow)sheet2.CreateRow(3);
                 HSSFRow vrow3 = (HSSFRow)sheet3.CreateRow(3);
                 HSSFRow vrow4 = (HSSFRow)sheet4.CreateRow(3);
                 HSSFRow vrow5 = (HSSFRow)sheet5.CreateRow(3);
                 HSSFRow vrow6 = (HSSFRow)sheet6.CreateRow(3);
                 HSSFRow vrow7 = (HSSFRow)sheet7.CreateRow(3);
                 if (!AreaCode.Equals("530000000000"))
                 {
                     workbook.Remove(sheet1);
                     workbook.Remove(sheet2);
                     workbook.Remove(sheet3);
                     workbook.Remove(sheet4);
                     workbook.Remove(sheet5);
                     workbook.Remove(sheet6);
                     workbook.Remove(sheet7);
                 }*/
                for (int i = 0; i < varietyList.Count; i++)
                {
                    HSSFSheet vsheet = templateBook.GetSheet("FruProSurvey") as HSSFSheet;
                    string varietyCode = varietyList[i].VarietyCode;
                    List<ProSurveyStatisModel> citylist = fruProSurveyServices.getOutStatisList(AreaCode, FillInYear, varietyCode).model;
                    vsheet = CopyToSheet(vsheet, citylist, DownType);                    
                    vsheet.CopyTo(workbook, varietyList[i].Variety, true, true);
                    /* if (AreaCode.Equals("530000000000"))
                     {
                         vrow1.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         vrow2.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         vrow3.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         vrow4.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         vrow5.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         vrow6.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         vrow7.CreateCell(i + 1).SetCellValue(varietyList[i].Variety);
                         List<ProSurveyStatisModel> countylist = fruProSurveyServices.getAllCountyStatisList(FillInYear, varietyCode);
                         for(int t = 0; t < countylist.Count; t++)
                         {
                             if (i == 0)
                             {
                                 HSSFRow drow1 = (HSSFRow)sheet1.CreateRow(4 + t);
                                 drow1.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow2 = (HSSFRow)sheet2.CreateRow(4 + t);
                                 drow2.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow3 = (HSSFRow)sheet3.CreateRow(4 + t);
                                 drow3.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow4 = (HSSFRow)sheet4.CreateRow(4 + t);
                                 drow4.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow5 = (HSSFRow)sheet5.CreateRow(4 + t);
                                 drow5.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow6 = (HSSFRow)sheet6.CreateRow(4 + t);
                                 drow6.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                                 HSSFRow drow7 = (HSSFRow)sheet7.CreateRow(4 + t);
                                 drow7.CreateCell(0).SetCellValue(countylist[t].AreaName == null ? " " : countylist[t].AreaName);
                             }
                             HSSFRow trow1 = (HSSFRow)sheet1.GetRow(4 + t);
                             trow1.CreateCell(i+1).SetCellValue(countylist[t].GrandArea == null ? "" : string.Format("{0:N1}", countylist[t].GrandArea));
                             HSSFRow trow2 = (HSSFRow)sheet2.GetRow(4 + t);
                             trow2.CreateCell(i + 1).SetCellValue(countylist[t].CumYield == null ? "" : string.Format("{0:N1}", countylist[t].CumYield));
                             HSSFRow trow3 = (HSSFRow)sheet3.GetRow(4 + t);
                             trow3.CreateCell(i + 1).SetCellValue(countylist[t].AvgMuYield == null ? "" : string.Format("{0:N1}", countylist[t].AvgMuYield));
                             HSSFRow trow4 = (HSSFRow)sheet4.GetRow(4 + t);
                             trow4.CreateCell(i + 1).SetCellValue(countylist[t].NewArea == null ? "" : string.Format("{0:N1}", countylist[t].NewArea));
                             HSSFRow trow5 = (HSSFRow)sheet5.GetRow(4 + t);
                             trow5.CreateCell(i + 1).SetCellValue(countylist[t].FOrchard == null ? "" : string.Format("{0:N1}", countylist[t].FOrchard));
                             HSSFRow trow6 = (HSSFRow)sheet6.GetRow(4 + t);
                             trow6.CreateCell(i + 1).SetCellValue(countylist[t].FTOrchard == null ? "" : string.Format("{0:N1}", countylist[t].FTOrchard));
                             HSSFRow trow7 = (HSSFRow)sheet7.GetRow(4 + t);
                             trow7.CreateCell(i + 1).SetCellValue(countylist[t].TOrchard == null ? "" : string.Format("{0:N1}", countylist[t].TOrchard));
                         }
                     }*/
                }
            }   
           
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public HSSFSheet CopyToSheet(HSSFSheet sheet, List<ProSurveyStatisModel> totallist, string DownType)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(3);
            ICellStyle style1 = sheet.Workbook.CreateCellStyle();
            ICellStyle style2 = sheet.Workbook.CreateCellStyle();
            if (totallist.Count > 0)
            {
                for (int i = 0; i < totallist.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(3 + i);
                    if (DownType.Equals("1"))
                    {
                        datarow.CreateCell(0).SetCellValue(totallist[i].Variety == null ? "" : totallist[i].Variety);
                    }
                    else
                    {
                        datarow.CreateCell(0).SetCellValue(totallist[i].AreaName == null ? "" : totallist[i].AreaName);
                    }
                    datarow.CreateCell(1).SetCellValue(totallist[i].GrandArea == null ? "" : string.Format("{0:N1}", totallist[i].GrandArea));
                    datarow.CreateCell(2).SetCellValue(totallist[i].CumYield == null ? "" : string.Format("{0:N1}", totallist[i].CumYield));
                    datarow.CreateCell(3).SetCellValue(totallist[i].AvgMuYield == null ? "" : string.Format("{0:N1}", totallist[i].AvgMuYield));
                    datarow.CreateCell(4).SetCellValue(totallist[i].NewArea == null ? "" : string.Format("{0:N1}", totallist[i].NewArea));
                    datarow.CreateCell(5).SetCellValue(totallist[i].FOrchard == null ? "" : string.Format("{0:N1}", totallist[i].FOrchard));
                    datarow.CreateCell(6).SetCellValue(totallist[i].FTOrchard == null ? "" : string.Format("{0:N1}", totallist[i].FTOrchard));
                    datarow.CreateCell(7).SetCellValue(totallist[i].TOrchard == null ? "" : string.Format("{0:N1}", totallist[i].TOrchard));
                    int tag = totallist[i].tag;
                    for (int j = 0; j <= 7; j++)
                    {
                        if (tag == 1)
                        {
                            style1.Alignment = row.GetCell(j).CellStyle.Alignment;
                            style1.FillPattern = FillPattern.SolidForeground;
                            style1.FillForegroundColor = HSSFColor.Yellow.Index;
                            datarow.GetCell(j).CellStyle = style1;
                        }
                        else if (tag == 2)
                        {
                            style2.Alignment = row.GetCell(j).CellStyle.Alignment;
                            style2.FillPattern = FillPattern.SolidForeground;
                            style2.FillForegroundColor = HSSFColor.Pink.Index;
                            datarow.GetCell(j).CellStyle = style2;
                        }
                        else
                        {
                            datarow.GetCell(j).CellStyle = row.GetCell(j).CellStyle;
                        }
                        
                    }
                    
                }
            }
            return sheet;
        }


    }
}